﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data.SqlClient;
using System.Data;

namespace DAO
{
    public class DatVeDAO : AbstractDAO<DatVeDTO>
    {
        protected override string GetTableName()
        {
            return "datve";
        }

        public DatVeDTO GetFullByDataRow(System.Data.DataRow data)
        {
            DatVeDTO datVe = new DatVeDTO();
            datVe.Id = int.Parse(data["id"].ToString());
            datVe.HoTen = data["hoten"].ToString();
            datVe.Chuyen = data["chuyen"].ToString();
            datVe.NhanVien = data["nhanvien"].ToString();
            datVe.Ghe = int.Parse(data["soghe"].ToString());
            datVe.NgayDatVe = DateTime.Parse(data["ngaydatve"].ToString());
            datVe.DaThanhToan = bool.Parse(data["dathanhtoan"].ToString());

            if (data["ngaythanhtoan"].GetType() == typeof(DateTime))
            {
                datVe.NgayThanhToan = DateTime.Parse(data["ngaythanhtoan"].ToString());
            }
            if (data["ngayhuy"].GetType() == typeof(DateTime))
            {
                datVe.NgayHuy = DateTime.Parse(data["ngayhuy"].ToString());
            }

            datVe.HuyDatVe = data["huydatve"].ToString() == "True";
            return datVe;
        }

        public int AddNew(DatVeDTO datVeDTO)
        {
            IList<SqlParameter> listParameter = new List<SqlParameter>();
            listParameter.Add(new SqlParameter("@KhachHangID", datVeDTO.KhachHangId));
            listParameter.Add(new SqlParameter("@ChuyenXeID", datVeDTO.ChuyenXeId));
            listParameter.Add(new SqlParameter("@NhanVienID", datVeDTO.NhanVienId));
            listParameter.Add(new SqlParameter("@SoGhe", datVeDTO.Ghe));
            listParameter.Add(new SqlParameter("@NgayDatVe", datVeDTO.NgayDatVe));

            SqlParameter sqlThongBao = new SqlParameter("@MaLoi", SqlDbType.Int);
            sqlThongBao.Direction = System.Data.ParameterDirection.Output;
            listParameter.Add(sqlThongBao);

            this.dataProvider.ExecuteStoreProc(
                "Proc_DatVe",
                listParameter);
            return (int)sqlThongBao.Value;
        }

        public override List<DatVeDTO> GetAll()
        {
            List<DatVeDTO> ls = new List<DatVeDTO>();
            String query = String.Format(
                "SELECT" +
                "   datve.id, " +
                "   khachhang.hoten, " +
                "   tuyenduong.noidi + ' - ' + " +
                "   tuyenduong.noiden + ' - ' + convert(varchar, chuyenxe.thoigianxuatphat, 101) chuyen, " +
                "   nhanvien.tendangnhap nhanvien, " +
                "   datve.soghe, " +
                "   datve.ngaydatve, " +
                "   datve.dathanhtoan, " +
                "   CAST( CASE " +
                "       WHEN datve.dathanhtoan = 0 and DATEADD(DAY,7, datve.ngaydatve) < GETDATE()  THEN 1 " +
                "      else 0 " +
                "   END as bit) | huydatve huydatve, " +
                "   datve.ngaythanhtoan, " +
                "   datve.ngayhuy " +
                "FROM " +
                "   datve INNER JOIN " +
                "   nhanvien ON datve.nhanvien_id = nhanvien.id INNER JOIN " +
                "   chuyenxe ON datve.chuyenxe_id = chuyenxe.id INNER JOIN " +
                "   khachhang ON datve.khachhang_id = khachhang.id INNER JOIN " +
                "   tuyenduong ON chuyenxe.tuyenduong_id = tuyenduong.id");
            DataTable dt = dataProvider.ExecuteQuery(query);
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow r in dt.Rows)
                {
                    DatVeDTO p = GetFullByDataRow(r);
                    ls.Add(p);
                }
            }
            return ls;
        }


        public override DatVeDTO GetByDataRow(DataRow data)
        {
            DatVeDTO datVe = new DatVeDTO();
            datVe.Id = int.Parse(data["id"].ToString());
            datVe.KhachHangId = int.Parse(data["khachhang_id"].ToString());
            datVe.ChuyenXeId = int.Parse(data["chuyenxe_id"].ToString());
            datVe.Ghe = int.Parse(data["soghe"].ToString());
            datVe.NgayDatVe = DateTime.Parse(data["ngaydatve"].ToString());
            datVe.DaThanhToan = bool.Parse(data["dathanhtoan"].ToString());

            if (data["ngaythanhtoan"].GetType() == typeof(DateTime))
            {
                datVe.NgayThanhToan = DateTime.Parse(data["ngaythanhtoan"].ToString());
            }
            if (data["ngayhuy"].GetType() == typeof(DateTime))
            {
                datVe.NgayHuy = DateTime.Parse(data["ngayhuy"].ToString());
            }

            datVe.HuyDatVe = bool.Parse(data["huydatve"].ToString());
            return datVe;
        }

        public bool ThanhToan(DatVeDTO datVeDTO, ref double giave, ref double tongtien, ref bool reset, ref double diemhientai)
        {
            IList<SqlParameter> listParameter = new List<SqlParameter>();
            listParameter.Add(new SqlParameter("@DatVeId", datVeDTO.Id));

            SqlParameter sqlReset = new SqlParameter("@Reset", SqlDbType.Bit);
            sqlReset.Direction = System.Data.ParameterDirection.Output;
            listParameter.Add(sqlReset);

            SqlParameter sqlGiaVe = new SqlParameter("@GiaVe", SqlDbType.Float);
            sqlGiaVe.Direction = System.Data.ParameterDirection.Output;
            listParameter.Add(sqlGiaVe);

            SqlParameter sqlTongTien = new SqlParameter("@TongTien", SqlDbType.Float);
            sqlTongTien.Direction = System.Data.ParameterDirection.Output;
            listParameter.Add(sqlTongTien);

            SqlParameter sqlDiemCongHienTai = new SqlParameter("@DiemCongHienTai", SqlDbType.Float);
            sqlDiemCongHienTai.Direction = System.Data.ParameterDirection.Output;
            listParameter.Add(sqlDiemCongHienTai);

            try
            {
                this.dataProvider.ExecuteStoreProc(
                    "Proc_ThanhToan",
                    listParameter);

                double.TryParse(sqlGiaVe.Value.ToString(), out giave);
                double.TryParse(sqlTongTien.Value.ToString(), out tongtien);
                bool.TryParse(sqlReset.Value.ToString(), out reset);
                double.TryParse(sqlDiemCongHienTai.Value.ToString(), out diemhientai);
            }
            catch (Exception e)
            {
                return false;
            }
            return true;
        }

        public bool HuyDatVe(DatVeDTO datVeDTO)
        {
            IList<SqlParameter> listParameter = new List<SqlParameter>();
            listParameter.Add(new SqlParameter("@DatVeId", datVeDTO.Id));
            listParameter.Add(new SqlParameter("@MaSo", datVeDTO.Ghe));
            try
            {
                this.dataProvider.ExecuteStoreProc(
                    "Proc_HuyDatVe",
                    listParameter);
            }
            catch (Exception e)
            {
                return false;
            }
            return true;
        }
    }
}
